/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package logic;

import entityBeans.Acceptednameusage;
import entityBeans.Associatedsequence;
import entityBeans.Basisofrecord;
import entityBeans.Behavior;
import entityBeans.Class;
import entityBeans.Collectioncode;
import entityBeans.Continent;
import entityBeans.Country;
import entityBeans.County;
import entityBeans.Dataset;
import entityBeans.Disposition;
import entityBeans.Dynamicproperty;
import entityBeans.Establishmentmean;
import entityBeans.Eventelement;
import entityBeans.Family;
import entityBeans.Genus;
import entityBeans.Georeferencedby;
import entityBeans.Georeferencesource;
import entityBeans.Georeferenceverificationstatus;
import entityBeans.Geospatialelement;
import entityBeans.Habitat;
import entityBeans.Identificationelement;
import entityBeans.Identificationqualifier;
import entityBeans.Identifiedby;
import entityBeans.Individual;
import entityBeans.Infraspecificepithet;
import entityBeans.Institutioncode;
import entityBeans.Interaction;
import entityBeans.Interactiontype;
import entityBeans.Island;
import entityBeans.Islandgroup;
import entityBeans.Kingdom;
import entityBeans.Lifestage;
import entityBeans.Locality;
import entityBeans.Localityelement;
import entityBeans.Municipality;
import entityBeans.Nameaccordingto;
import entityBeans.Namepublishedin;
import entityBeans.Nomenclaturalcode;
import entityBeans.Occurrenceelement;
import entityBeans.Order1;
import entityBeans.Originalnameusage;
import entityBeans.Ownerinstitution;
import entityBeans.Parentnameusage;
import entityBeans.Phylum;
import entityBeans.Preparation;
import entityBeans.Recordedby;
import entityBeans.Recordlevelelement;
import entityBeans.Reproductivecondition;
import entityBeans.Samplingprotocol;
import entityBeans.Scientificname;
import entityBeans.Scientificnameauthorship;
import entityBeans.Sex;
import entityBeans.Specificepithet;
import entityBeans.Specimen;
import entityBeans.Stateprovince;
import entityBeans.Subgenus;
import entityBeans.Taxonconcept;
import entityBeans.Taxonomicelement;
import entityBeans.Taxonomicstatus;
import entityBeans.Taxonrank;
import entityBeans.Type;
import entityBeans.Typestatus;
import entityBeans.Waterbody;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.postgresql.util.PSQLException;
import persistence.EventelementJpaController;
import persistence.GeospatialelementJpaController;
import persistence.IdentificationelementJpaController;
import persistence.LocalityelementJpaController;
import persistence.OccurrenceelementJpaController;
import persistence.RecordlevelelementJpaController;
import persistence.SpecimenJpaController;
import persistence.TaxonomicelementJpaController;
import xlsBeans.Filter;

/**
 *
 * @author allankv
 */
public class DatabaseHandling {

    private SpecimenJpaController cSp = new SpecimenJpaController();
    private SQLGenerate sqlGenerate = new SQLGenerate();
    private SaveTables saveTables;
    private String sqlSpecimen;
    private Connection conn;
    private boolean add;

    private RecordlevelelementJpaController cRec = new RecordlevelelementJpaController();
    private TaxonomicelementJpaController cTax = new TaxonomicelementJpaController();
    private LocalityelementJpaController cLoc = new LocalityelementJpaController();
    private OccurrenceelementJpaController cOcc = new OccurrenceelementJpaController();
    private GeospatialelementJpaController cGeo = new GeospatialelementJpaController();
    private EventelementJpaController cEve = new EventelementJpaController();
    private IdentificationelementJpaController cIde = new IdentificationelementJpaController();

    private List<Basisofrecord> cacheBasisofrecord = new ArrayList<Basisofrecord>();
    private List<Institutioncode> cacheInstitutioncode = new ArrayList<Institutioncode>();
    private List<Collectioncode> cacheCollectioncode = new ArrayList<Collectioncode>();
    private List<Ownerinstitution> cacheOwnerinstitution = new ArrayList<Ownerinstitution>();
    private List<Dataset> cacheDataset = new ArrayList<Dataset>();
    private List<Type> cacheType = new ArrayList<Type>();
    private List<Behavior> cacheBehavior = new ArrayList<Behavior>();
    private List<Disposition> cacheDisposition = new ArrayList<Disposition>();
    private List<Establishmentmean> cacheEstablishmentmean = new ArrayList<Establishmentmean>();
    private List<Lifestage> cacheLifestage = new ArrayList<Lifestage>();
    private List<Reproductivecondition> cacheReproductivecondition = new ArrayList<Reproductivecondition>();
    private List<Sex> cacheSex = new ArrayList<Sex>();
    private List<Acceptednameusage> cacheAcceptednameusage = new ArrayList<Acceptednameusage>();
    private List<Class> cacheClass = new ArrayList<Class>();
    private List<Family> cacheFamily = new ArrayList<Family>();
    private List<Genus> cacheGenus = new ArrayList<Genus>();
    private List<Infraspecificepithet> cacheInfraspecificepithet = new ArrayList<Infraspecificepithet>();
    private List<Kingdom> cacheKingdom = new ArrayList<Kingdom>();
    private List<Nameaccordingto> cacheNameaccordingto = new ArrayList<Nameaccordingto>();
    private List<Namepublishedin> cacheNamepublishedin = new ArrayList<Namepublishedin>();
    private List<Nomenclaturalcode> cacheNomenclaturalcode = new ArrayList<Nomenclaturalcode>();
    private List<Order1> cacheOrder1 = new ArrayList<Order1>();
    private List<Originalnameusage> cacheOriginalnameusage = new ArrayList<Originalnameusage>();
    private List<Parentnameusage> cacheParentnameusage = new ArrayList<Parentnameusage>();
    private List<Phylum> cachePhylum = new ArrayList<Phylum>();
    private List<Scientificname> cacheScientificname = new ArrayList<Scientificname>();
    private List<Scientificnameauthorship> cacheScientificnameauthorship = new ArrayList<Scientificnameauthorship>();
    private List<Specificepithet> cacheSpecificepithet = new ArrayList<Specificepithet>();
    private List<Subgenus> cacheSubgenus = new ArrayList<Subgenus>();
    private List<Taxonconcept> cacheTaxonconcept = new ArrayList<Taxonconcept>();
    private List<Taxonomicstatus> cacheTaxonomicstatus = new ArrayList<Taxonomicstatus>();
    private List<Taxonrank> cacheTaxonrank = new ArrayList<Taxonrank>();
    private List<Identificationqualifier> cacheIdentificationqualifier = new ArrayList<Identificationqualifier>();
    private List<Habitat> cacheHabitat = new ArrayList<Habitat>();
    private List<Samplingprotocol> cacheSamplingprotocol = new ArrayList<Samplingprotocol>();
    private List<Georeferenceverificationstatus> cacheGeoreferenceverificationstatus = new ArrayList<Georeferenceverificationstatus>();
    private List<Continent> cacheContinent = new ArrayList<Continent>();
    private List<Country> cacheCountry = new ArrayList<Country>();
    private List<County> cacheCounty = new ArrayList<County>();
    private List<Island> cacheIsland = new ArrayList<Island>();
    private List<Islandgroup> cacheIslandgroup = new ArrayList<Islandgroup>();
    private List<Locality> cacheLocality = new ArrayList<Locality>();
    private List<Municipality> cacheMunicipality = new ArrayList<Municipality>();
    private List<Stateprovince> cacheStateprovince = new ArrayList<Stateprovince>();
    private List<Waterbody> cacheWaterbody = new ArrayList<Waterbody>();
    private List<Dynamicproperty> cacheDynamicpropertyNN = new ArrayList<Dynamicproperty>();
    private List<Associatedsequence> cacheAssociatedsequenceNN = new ArrayList<Associatedsequence>();
    private List<Individual> cacheIndividualNN = new ArrayList<Individual>();
    private List<Preparation> cachePreparationNN = new ArrayList<Preparation>();
    private List<Recordedby> cacheRecordedbyNN = new ArrayList<Recordedby>();
    private List<Identifiedby> cacheIdentifiedbyNN = new ArrayList<Identifiedby>();
    private List<Typestatus> cacheTypestatusNN = new ArrayList<Typestatus>();
    private List<Georeferencedby> cacheGeoreferencedbyNN = new ArrayList<Georeferencedby>();
    private List<Georeferencesource> cacheGeoreferencesourceNN = new ArrayList<Georeferencesource>();

    public DatabaseHandling() throws SQLException, ClassNotFoundException {
        java.lang.Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection("jdbc:postgresql://10.0.10.102:5432/bdd_desenv?user=bdd_desenv&password=bdd_d3s3nv");

        this.saveTables = new SaveTables(conn);

        cRec = new RecordlevelelementJpaController();
        cTax = new TaxonomicelementJpaController();
        cLoc = new LocalityelementJpaController();
        cOcc = new OccurrenceelementJpaController();
        cGeo = new GeospatialelementJpaController();
        cEve = new EventelementJpaController();
        cIde = new IdentificationelementJpaController();

//       Basisofrecord HumanObservation = new Basisofrecord(20, "Human Observation");
//       cacheBasisofrecord.add(HumanObservation);
//       Basisofrecord PreservedSpecimen = new Basisofrecord(17, "Preserved Specimen");
//       cacheBasisofrecord.add(PreservedSpecimen);
//       Basisofrecord MachineObservation = new Basisofrecord(21, "Machine Observation");
//       cacheBasisofrecord.add(MachineObservation);
//       Basisofrecord FossilSpecimen = new Basisofrecord(18, "Fossil Specimen");
//       cacheBasisofrecord.add(FossilSpecimen);
//       Basisofrecord LivingSpecimen = new Basisofrecord(19, "Living Specimen");
//       cacheBasisofrecord.add(LivingSpecimen);
//       Basisofrecord Occurrence = new Basisofrecord(16, "Occurrence");
//       cacheBasisofrecord.add(Occurrence);
//       Basisofrecord Taxon = new Basisofrecord(22, "Taxon");
//       cacheBasisofrecord.add(Taxon);
//       Basisofrecord Location = new Basisofrecord(23, "Location");
//       cacheBasisofrecord.add(Location);
//       Basisofrecord NomenclaturalChecklist = new Basisofrecord(24, "Nomenclatural Checklist");
//       cacheBasisofrecord.add(NomenclaturalChecklist);

        //Basisofrecord Species = new Basisofrecord(, "Species");

    }

    public List<Specimen> findAllSpecimen(List<Filter> f) throws ClassNotFoundException, SQLException {
        String where = new String();

        sqlSpecimen = sqlGenerate.specimenSearchSQL();
        where = sqlGenerate.whereSQL(f);
        
        Statement stm = conn.createStatement();
        ResultSet resultset = stm.executeQuery(sqlSpecimen + where);

        List<Specimen> list = new ArrayList<Specimen>();

        while (resultset.next()) {
            Specimen sp = fillSpecimen(resultset);
            //pega interacoes
            Statement stmInte = conn.createStatement();
            List<Interaction> listInte = new ArrayList<Interaction>();
            ResultSet resultsetInte = stmInte.executeQuery(
                    "select * from interaction i0 "
                    + "left join interactiontype i1 ON i1.idinteractiontype= i0.idinteractiontype "
                    + "where idspecimen1 = " + sp.getIdspecimen());
            while (resultsetInte.next()) {
                Interaction inteAux = new Interaction();
                inteAux.setSpecimen1(sp);
                inteAux.setIdinteraction(resultsetInte.getInt("idinteraction"));
                inteAux.setInteractionrelatedinformation(resultsetInte.getString("interactionrelatedinformation"));
                Interactiontype it = new Interactiontype();
                it.setInteractiontype(resultsetInte.getString("interactiontype"));
                inteAux.setInteractiontype(it);

                Statement stmSp2 = conn.createStatement();
                ResultSet resultsetSp2 = stmSp2.executeQuery(
                        sqlSpecimen + " where idspecimen = " + resultsetInte.getInt("idspecimen2"));
                while (resultsetSp2.next()) {
                    inteAux.setSpecimen(fillSpecimen(resultsetSp2));
                }
                listInte.add(inteAux);
            }
            sp.setInteractionList(listInte);
            list.add(sp);
        }


        return list;
    }

    public Specimen fillSpecimen(ResultSet resultset) throws SQLException {
        //Recordlevel
        Basisofrecord r1 = new Basisofrecord();
        r1.setBasisofrecord(resultset.getString("basisofrecord"));
        Institutioncode r2 = new Institutioncode();
        r2.setInstitutioncode(resultset.getString("institutioncode"));
        Collectioncode r3 = new Collectioncode();
        r3.setCollectioncode(resultset.getString("collectioncode"));
        /*        Language r4 = new Language();
        r4.setLanguage(resultset.getString("language"));*/
        Dataset r5 = new Dataset();
        r5.setDataset(resultset.getString("dataset"));
        Ownerinstitution r6 = new Ownerinstitution();
        r6.setOwnerinstitution(resultset.getString("ownerinstitution"));
        Type r7 = new Type();
        r7.setType(resultset.getString("type"));

        Recordlevelelement r0 = new Recordlevelelement();
        r0.setIdrecordlevelelement(resultset.getInt("idrecordlevelelement"));
        r0.setGlobaluniqueidentifier(resultset.getString("globaluniqueidentifier"));
        r0.setRights(resultset.getString("rights"));
        r0.setRightsholder(resultset.getString("rightsholder"));
        r0.setAccessrights(resultset.getString("accessrights"));
        r0.setInformationwithheld(resultset.getString("informationwithheld"));
        r0.setDatageneralization(resultset.getString("datageneralization"));
        r0.setIsrestricted(resultset.getBoolean("isrestricted"));
        r0.setBasisofrecord(r1);
        r0.setInstitutioncode(r2);
        r0.setCollectioncode(r3);
        //r0.setLanguage(r4);
        r0.setDataset(r5);
        r0.setOwnerinstitution(r6);
        r0.setType(r7);

        //Occurence
        Behavior o1 = new Behavior();
        o1.setBehavior(resultset.getString("behavior"));
        Disposition o2 = new Disposition();
        o2.setDisposition(resultset.getString("Disposition"));
        Establishmentmean o3 = new Establishmentmean();
        o3.setEstablishmentmean(resultset.getString("Establishmentmean"));
        Lifestage o4 = new Lifestage();
        o4.setLifestage(resultset.getString("Lifestage"));
        Reproductivecondition o5 = new Reproductivecondition();
        o5.setReproductivecondition(resultset.getString("Reproductivecondition"));
        Sex o6 = new Sex();
        o6.setSex(resultset.getString("Sex"));

        Occurrenceelement o0 = new Occurrenceelement();
        o0.setIdoccurrenceelement(resultset.getInt("idoccurrenceelement"));
        o0.setCatalognumber(resultset.getString("catalognumber"));
        o0.setIndividualcount(resultset.getString("individualcount")==null?null:resultset.getInt("individualcount"));
        o0.setRecordnumber(resultset.getString("recordnumber"));
        o0.setOthercatalognumber(resultset.getString("othercatalognumber"));
        o0.setOccurrencedetail(resultset.getString("occurrencedetail"));
        o0.setOccurrenceremark(resultset.getString("occurrenceremark"));
        o0.setOccurrencestatus(resultset.getString("occurrencestatus"));
        o0.setBehavior(o1);
        o0.setDisposition(o2);
        o0.setEstablishmentmean(o3);
        o0.setLifestage(o4);
        o0.setReproductivecondition(o5);
        o0.setSex(o6);

        //Taxonomic
        Acceptednameusage t1 = new Acceptednameusage();
        t1.setAcceptednameusage(resultset.getString("Acceptednameusage"));
        entityBeans.Class t2 = new entityBeans.Class();
        t2.setClass1(resultset.getString("class"));
        Family t3 = new Family();
        t3.setFamily(resultset.getString("Family"));
        Genus t4 = new Genus();
        t4.setGenus(resultset.getString("Genus"));
        Infraspecificepithet t5 = new Infraspecificepithet();
        t5.setInfraspecificepithet(resultset.getString("Infraspecificepithet"));
        Kingdom t6 = new Kingdom();
        t6.setKingdom(resultset.getString("Kingdom"));
        Nameaccordingto t7 = new Nameaccordingto();
        t7.setNameaccordingto(resultset.getString("Nameaccordingto"));
        Namepublishedin t8 = new Namepublishedin();
        t8.setNamepublishedin(resultset.getString("Namepublishedin"));
        Nomenclaturalcode t9 = new Nomenclaturalcode();
        t9.setNomenclaturalcode(resultset.getString("Nomenclaturalcode"));
        Order1 t10 = new Order1();
        t10.setOrder(resultset.getString("order"));
        Originalnameusage t11 = new Originalnameusage();
        t11.setOriginalnameusage(resultset.getString("Originalnameusage"));
        Parentnameusage t12 = new Parentnameusage();
        t12.setParentnameusage(resultset.getString("Parentnameusage"));
        Phylum t13 = new Phylum();
        t13.setPhylum(resultset.getString("Phylum"));
        Scientificname t14 = new Scientificname();
        t14.setScientificname(resultset.getString("Scientificname"));
        Scientificnameauthorship t15 = new Scientificnameauthorship();
        t15.setScientificnameauthorship(resultset.getString("Scientificnameauthorship"));
        Specificepithet t16 = new Specificepithet();
        t16.setSpecificepithet(resultset.getString("Specificepithet"));
        Subgenus t17 = new Subgenus();
        t17.setSubgenus(resultset.getString("Subgenus"));
        Taxonconcept t18 = new Taxonconcept();
        t18.setTaxonconcept(resultset.getString("Taxonconcept"));
        Taxonomicstatus t19 = new Taxonomicstatus();
        t19.setTaxonomicstatus(resultset.getString("Taxonomicstatus"));
        Taxonrank t20 = new Taxonrank();
        t20.setTaxonrank(resultset.getString("Taxonrank"));

        Taxonomicelement t0 = new Taxonomicelement();
        t0.setIdtaxonomicelement(resultset.getInt("idtaxonomicelement"));
        t0.setNomenclaturalstatus(resultset.getString("nomenclaturalstatus"));
        t0.setVernacularname(resultset.getString("vernacularname"));
        t0.setVerbatimtaxonrank(resultset.getString("verbatimtaxonrank"));
        t0.setTaxonremark(resultset.getString("taxonremark"));
        t0.setAcceptednameusage(t1);
        t0.setClass1(t2);
        t0.setFamily(t3);
        t0.setGenus(t4);
        t0.setInfraspecificepithet(t5);
        t0.setKingdom(t6);
        t0.setNameaccordingto(t7);
        t0.setNamepublishedin(t8);
        t0.setNomenclaturalcode(t9);
        t0.setOrder1(t10);
        t0.setOriginalnameusage(t11);
        t0.setParentnameusage(t12);
        t0.setPhylum(t13);
        t0.setScientificname(t14);
        t0.setScientificnameauthorship(t15);
        t0.setSpecificepithet(t16);
        t0.setSubgenus(t17);
        t0.setTaxonconcept(t18);
        t0.setTaxonomicstatus(t19);
        t0.setTaxonrank(t20);

        //Identif
        Identificationqualifier i1 = new Identificationqualifier();
        i1.setIdentificationqualifier(resultset.getString("Identificationqualifier"));

        Identificationelement i0 = new Identificationelement();
        i0.setIdidentificationelement(resultset.getInt("ididentificationelement"));
        i0.setDateidentified(resultset.getDate("dateidentified"));
        i0.setIdentificationremark(resultset.getString("identificationremark"));
        i0.setIdentificationqualifier(i1);

        //Event
        Habitat e1 = new Habitat();
        e1.setHabitat(resultset.getString("Habitat"));
        Samplingprotocol e2 = new Samplingprotocol();
        e2.setSamplingprotocol(resultset.getString("Samplingprotocol"));

        Eventelement e0 = new Eventelement();
        e0.setIdeventelement(resultset.getInt("ideventelement"));
        e0.setSamplingeffort(resultset.getString("samplingeffort"));
        e0.setVerbatimeventdate(resultset.getString("verbatimeventdate"));
        e0.setFieldnumber(resultset.getString("fieldnumber"));
        e0.setFieldnote(resultset.getString("fieldnote"));
        e0.setEventremark(resultset.getString("eventremark"));
        e0.setEventtime(resultset.getTime("eventtime"));
        e0.setEventdate(resultset.getDate("eventdate"));
        e0.setHabitat(e1);
        e0.setSamplingprotocol(e2);
        e0.setEventdate(resultset.getDate("eventdate"));



        //Locality
        Continent l1 = new Continent();
        l1.setContinent(resultset.getString("Continent"));
        Country l2 = new Country();
        l2.setCountry(resultset.getString("Country"));
        Stateprovince l3 = new Stateprovince();
        l3.setStateprovince(resultset.getString("Stateprovince"));
        County l4 = new County();
        l4.setCounty(resultset.getString("County"));
        Municipality l5 = new Municipality();
        l5.setMunicipality(resultset.getString("Municipality"));
        Locality l6 = new Locality();
        l6.setLocality(resultset.getString("Locality"));
        Waterbody l7 = new Waterbody();
        l7.setWaterbody(resultset.getString("Waterbody"));
        Islandgroup l8 = new Islandgroup();
        l8.setIslandgroup(resultset.getString("Islandgroup"));
        Island l9 = new Island();
        l9.setIsland(resultset.getString("Island"));

        Localityelement l0 = new Localityelement();
        l0.setIdlocalityelement(resultset.getInt("idlocalityelement"));
        l0.setLocationaccordingto(resultset.getString("locationaccordingto"));
        l0.setCoordinateprecision(resultset.getString("coordinateprecision"));
        l0.setLocationremark(resultset.getString("locationremark"));
        l0.setMinimumelevationinmeters(resultset.getString("minimumelevationinmeters"));
        l0.setMaximumelevationinmeters(resultset.getString("maximumelevationinmeters"));
        l0.setMinimumdepthinmeters(resultset.getString("minimumdepthinmeters"));
        l0.setMaximumdepthinmeters(resultset.getString("maximumdepthinmeters"));
        l0.setMinimumdistanceabovesurfaceinmeters(resultset.getString("minimumdistanceabovesurfaceinmeters")==null?null:resultset.getFloat("minimumdistanceabovesurfaceinmeters"));
        l0.setMaximumdistanceabovesurfaceinmeters(resultset.getString("maximumdistanceabovesurfaceinmeters")==null?null:resultset.getFloat("maximumdistanceabovesurfaceinmeters"));
        l0.setVerbatimdepth(resultset.getString("verbatimdepth"));
        l0.setVerbatimelevation(resultset.getString("verbatimelevation"));
        l0.setVerbatimlocality(resultset.getString("verbatimlocality"));
        l0.setVerbatimsrs(resultset.getString("verbatimsrs"));
        l0.setFootprintsrs(resultset.getString("footprintsrs"));
        l0.setContinent(l1);
        l0.setCountry(l2);
        l0.setStateprovince(l3);
        l0.setCounty(l4);
        l0.setMunicipality(l5);
        l0.setLocality(l6);
        l0.setWaterbody(l7);
        l0.setIslandgroup(l8);
        l0.setIsland(l9);

        Georeferenceverificationstatus g1 = new Georeferenceverificationstatus();
        g1.setGeoreferenceverificationstatus(resultset.getString("georeferenceverificationstatus"));

        Geospatialelement g0 = new Geospatialelement();
        g0.setIdgeospatialelement(resultset.getInt("idgeospatialelement"));
        g0.setDecimallatitude(resultset.getString("decimallatitude")==null?null:resultset.getDouble("decimallatitude"));
        g0.setDecimallongitude(resultset.getString("decimallongitude")==null?null:resultset.getDouble("decimallongitude"));
        g0.setCoordinateuncertaintyinmeters(resultset.getString("coordinateuncertaintyinmeters")==null?null:resultset.getDouble("coordinateuncertaintyinmeters"));
        g0.setGeodeticdatum(resultset.getString("geodeticdatum"));
        g0.setPointradiusspatialfit(resultset.getString("pointradiusspatialfit"));
        g0.setVerbatimcoordinate(resultset.getString("verbatimcoordinate"));
        g0.setVerbatimlatitude(resultset.getString("verbatimlatitude"));
        g0.setVerbatimlongitude(resultset.getString("verbatimlongitude"));
        g0.setVerbatimcoordinatesystem(resultset.getString("verbatimcoordinatesystem"));
        g0.setGeoreferenceprotocol(resultset.getString("georeferenceprotocol"));
        g0.setGeoreferenceremark(resultset.getString("georeferenceremark"));
        g0.setFootprintwkt(resultset.getString("footprintwkt"));
        g0.setFootprintspatialfit(resultset.getString("footprintspatialfit"));
        g0.setGeoreferenceverificationstatus(g1);

        //seta specimen
        Specimen sp = new Specimen();
        sp.setIdspecimen(resultset.getInt("idspecimen"));
        sp.setRecordlevelelement(r0);
        sp.setOccurrenceelement(o0);
        sp.setTaxonomicelement(t0);
        sp.setIdentificationelement(i0);
        sp.setEventelement(e0);
        sp.setLocalityelement(l0);
        sp.setGeospatialelement(g0);

        List<Dynamicproperty> dyList = new ArrayList<Dynamicproperty>();
        Dynamicproperty dyn = new Dynamicproperty(null, resultset.getString("dynamicproperty"));
        dyList.add(dyn);

        sp.getRecordlevelelement().setDynamicpropertyList(dyList);
        List<Individual> indList = new ArrayList<Individual>();
        Individual ind = new Individual();
        ind.setIndividual(resultset.getString("individual"));
        indList.add(ind);
        sp.getOccurrenceelement().setIndividualList(indList);

        List<Recordedby> rbList = new ArrayList<Recordedby>();
        Recordedby rby = new Recordedby();
        rby.setRecordedby(resultset.getString("recordedby"));
        rbList.add(rby);
        sp.getOccurrenceelement().setRecordedbyList(rbList);

        List<Preparation> prepList = new ArrayList<Preparation>();
        Preparation prep = new Preparation();
        prep.setPreparation(resultset.getString("preparation"));
        prepList.add(prep);
        sp.getOccurrenceelement().setPreparationList(prepList);

        List<Associatedsequence> asList = new ArrayList<Associatedsequence>();
        Associatedsequence as = new Associatedsequence();
        as.setAssociatedsequence(resultset.getString("associatedsequence"));
        asList.add(as);
        sp.getOccurrenceelement().setAssociatedsequenceList(asList);

        List<Identifiedby> ibList = new ArrayList<Identifiedby>();
        Identifiedby ib = new Identifiedby();
        ib.setIdentifiedby(resultset.getString("identifiedby"));
        ibList.add(ib);
        sp.getIdentificationelement().setIdentifiedbyList(ibList);

        List<Typestatus> tsList = new ArrayList<Typestatus>();
        Typestatus ts = new Typestatus();
        ts.setTypestatus(resultset.getString("typestatus"));
        tsList.add(ts);
        sp.getIdentificationelement().setTypestatusList(tsList);

        List<Georeferencedby> gbList = new ArrayList<Georeferencedby>();
        Georeferencedby gb = new Georeferencedby();
        gb.setGeoreferencedby(resultset.getString("georeferencedby"));
        gbList.add(gb);
        sp.getLocalityelement().setGeoreferencedbyList(gbList);

        List<Georeferencesource> gsList = new ArrayList<Georeferencesource>();
        Georeferencesource gs = new Georeferencesource();
        gs.setGeoreferencesource(resultset.getString("georeferencesource"));
        gsList.add(gs);
        sp.getLocalityelement().setGeoreferencesourceList(gsList);

        return sp;
    }

    public void generateCache(List<Specimen> listSpecimen) throws SQLException {
        for (Specimen sp : listSpecimen) {
            System.out.println(sp.getRecordlevelelement().getOwnerinstitution().getIdownerinstitution());
            if (cacheBasisofrecord.isEmpty()) {
                cacheBasisofrecord.add(sp.getRecordlevelelement().getBasisofrecord());
            }
            add = true;
            for (Basisofrecord j : cacheBasisofrecord) {
                if (j.getBasisofrecord().equals(sp.getRecordlevelelement().getBasisofrecord().getBasisofrecord())) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheBasisofrecord.add(sp.getRecordlevelelement().getBasisofrecord());
            }

            if (cacheInstitutioncode.isEmpty()) {
                cacheInstitutioncode.add(sp.getRecordlevelelement().getInstitutioncode());
            }
            add = true;
            for (Institutioncode j : cacheInstitutioncode) {
                if (j.getInstitutioncode().equals(sp.getRecordlevelelement().getInstitutioncode().getInstitutioncode())) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheInstitutioncode.add(sp.getRecordlevelelement().getInstitutioncode());
            }

            if (cacheCollectioncode.isEmpty()) {
                cacheCollectioncode.add(sp.getRecordlevelelement().getCollectioncode());
            }
            add = true;
            for (Collectioncode j : cacheCollectioncode) {
                if (j.getCollectioncode().equals(sp.getRecordlevelelement().getCollectioncode().getCollectioncode())) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheCollectioncode.add(sp.getRecordlevelelement().getCollectioncode());
            }

            if (cacheOwnerinstitution.isEmpty() && !sp.getRecordlevelelement().getOwnerinstitution().getOwnerinstitution().equals("")) {
                cacheOwnerinstitution.add(sp.getRecordlevelelement().getOwnerinstitution());
            }
            add = true;
            for (Ownerinstitution j : cacheOwnerinstitution) {
                if (j.getOwnerinstitution().equals(sp.getRecordlevelelement().getOwnerinstitution().getOwnerinstitution()) || sp.getRecordlevelelement().getOwnerinstitution().getOwnerinstitution().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheOwnerinstitution.add(sp.getRecordlevelelement().getOwnerinstitution());
            }

            if (cacheDataset.isEmpty() && !sp.getRecordlevelelement().getDataset().getDataset().equals("")) {
                cacheDataset.add(sp.getRecordlevelelement().getDataset());
            }
            add = true;
            for (Dataset j : cacheDataset) {
                if (j.getDataset().equals(sp.getRecordlevelelement().getDataset().getDataset()) || sp.getRecordlevelelement().getDataset().getDataset().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheDataset.add(sp.getRecordlevelelement().getDataset());
            }

            if (cacheType.isEmpty() && !sp.getRecordlevelelement().getType().getType().equals("")) {
                cacheType.add(sp.getRecordlevelelement().getType());
            }
            add = true;
            for (Type j : cacheType) {
                if (j.getType().equals(sp.getRecordlevelelement().getType().getType()) || sp.getRecordlevelelement().getType().getType().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheType.add(sp.getRecordlevelelement().getType());
            }

            //occ
            if (cacheBehavior.isEmpty() && !sp.getOccurrenceelement().getBehavior().getBehavior().equals("")) {
                cacheBehavior.add(sp.getOccurrenceelement().getBehavior());
            }
            add = true;
            for (Behavior j : cacheBehavior) {
                if (j.getBehavior().equals(sp.getOccurrenceelement().getBehavior().getBehavior()) || sp.getOccurrenceelement().getBehavior().getBehavior().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheBehavior.add(sp.getOccurrenceelement().getBehavior());
            }

            if (cacheDisposition.isEmpty() && !sp.getOccurrenceelement().getDisposition().getDisposition().equals("")) {
                cacheDisposition.add(sp.getOccurrenceelement().getDisposition());
            }
            add = true;
            for (Disposition j : cacheDisposition) {
                if (j.getDisposition().equals(sp.getOccurrenceelement().getDisposition().getDisposition()) || sp.getOccurrenceelement().getDisposition().getDisposition().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheDisposition.add(sp.getOccurrenceelement().getDisposition());
            }
            if (cacheEstablishmentmean.isEmpty() && !sp.getOccurrenceelement().getEstablishmentmean().getEstablishmentmean().equals("")) {
                cacheEstablishmentmean.add(sp.getOccurrenceelement().getEstablishmentmean());
            }
            add = true;
            for (Establishmentmean j : cacheEstablishmentmean) {
                if (j.getEstablishmentmean().equals(sp.getOccurrenceelement().getEstablishmentmean().getEstablishmentmean()) || sp.getOccurrenceelement().getEstablishmentmean().getEstablishmentmean().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheEstablishmentmean.add(sp.getOccurrenceelement().getEstablishmentmean());
            }
            if (cacheLifestage.isEmpty() && !sp.getOccurrenceelement().getLifestage().getLifestage().equals("")) {
                cacheLifestage.add(sp.getOccurrenceelement().getLifestage());
            }
            add = true;
            for (Lifestage j : cacheLifestage) {
                if (j.getLifestage().equals(sp.getOccurrenceelement().getLifestage().getLifestage()) || sp.getOccurrenceelement().getLifestage().getLifestage().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheLifestage.add(sp.getOccurrenceelement().getLifestage());
            }
            if (cacheReproductivecondition.isEmpty() && !sp.getOccurrenceelement().getReproductivecondition().getReproductivecondition().equals("")) {
                cacheReproductivecondition.add(sp.getOccurrenceelement().getReproductivecondition());
            }
            add = true;
            for (Reproductivecondition j : cacheReproductivecondition) {
                if (j.getReproductivecondition().equals(sp.getOccurrenceelement().getReproductivecondition().getReproductivecondition()) || sp.getOccurrenceelement().getReproductivecondition().getReproductivecondition().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheReproductivecondition.add(sp.getOccurrenceelement().getReproductivecondition());
            }
            if (cacheSex.isEmpty() && !sp.getOccurrenceelement().getSex().getSex().equals("")) {
                cacheSex.add(sp.getOccurrenceelement().getSex());
            }
            add = true;
            for (Sex j : cacheSex) {
                if (j.getSex().equals(sp.getOccurrenceelement().getSex().getSex()) || sp.getOccurrenceelement().getSex().getSex().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheSex.add(sp.getOccurrenceelement().getSex());
            }
            //taxon
            if (cacheAcceptednameusage.isEmpty() && !sp.getTaxonomicelement().getAcceptednameusage().getAcceptednameusage().equals("")) {
                cacheAcceptednameusage.add(sp.getTaxonomicelement().getAcceptednameusage());
            }
            add = true;
            for (Acceptednameusage j : cacheAcceptednameusage) {
                if (j.getAcceptednameusage().equals(sp.getTaxonomicelement().getAcceptednameusage().getAcceptednameusage()) || sp.getTaxonomicelement().getAcceptednameusage().getAcceptednameusage().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheAcceptednameusage.add(sp.getTaxonomicelement().getAcceptednameusage());
            }
            if (cacheClass.isEmpty() && !sp.getTaxonomicelement().getClass1().getClass1().equals("")) {
                cacheClass.add(sp.getTaxonomicelement().getClass1());
            }
            add = true;
            for (Class j : cacheClass) {
                if (j.getClass1().equals(sp.getTaxonomicelement().getClass1().getClass1()) || sp.getTaxonomicelement().getClass1().getClass1().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheClass.add(sp.getTaxonomicelement().getClass1());
            }
            if (cacheFamily.isEmpty() && !sp.getTaxonomicelement().getFamily().getFamily().equals("")) {
                cacheFamily.add(sp.getTaxonomicelement().getFamily());
            }
            add = true;
            for (Family j : cacheFamily) {
                if (j.getFamily().equals(sp.getTaxonomicelement().getFamily().getFamily()) || sp.getTaxonomicelement().getFamily().getFamily().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheFamily.add(sp.getTaxonomicelement().getFamily());
            }
            if (cacheGenus.isEmpty() && !sp.getTaxonomicelement().getGenus().getGenus().equals("")) {
                cacheGenus.add(sp.getTaxonomicelement().getGenus());
            }
            add = true;
            for (Genus j : cacheGenus) {
                if (j.getGenus().equals(sp.getTaxonomicelement().getGenus().getGenus()) || sp.getTaxonomicelement().getGenus().getGenus().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheGenus.add(sp.getTaxonomicelement().getGenus());
            }
            if (cacheInfraspecificepithet.isEmpty() && !sp.getTaxonomicelement().getInfraspecificepithet().getInfraspecificepithet().equals("")) {
                cacheInfraspecificepithet.add(sp.getTaxonomicelement().getInfraspecificepithet());
            }
            add = true;
            for (Infraspecificepithet j : cacheInfraspecificepithet) {
                if (j.getInfraspecificepithet().equals(sp.getTaxonomicelement().getInfraspecificepithet().getInfraspecificepithet()) || sp.getTaxonomicelement().getInfraspecificepithet().getInfraspecificepithet().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheInfraspecificepithet.add(sp.getTaxonomicelement().getInfraspecificepithet());
            }
            if (cacheKingdom.isEmpty() && !sp.getTaxonomicelement().getKingdom().getKingdom().equals("")) {
                cacheKingdom.add(sp.getTaxonomicelement().getKingdom());
            }
            add = true;
            for (Kingdom j : cacheKingdom) {
                if (j.getKingdom().equals(sp.getTaxonomicelement().getKingdom().getKingdom()) || sp.getTaxonomicelement().getKingdom().getKingdom().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheKingdom.add(sp.getTaxonomicelement().getKingdom());
            }
            if (cacheNameaccordingto.isEmpty() && !sp.getTaxonomicelement().getNameaccordingto().getNameaccordingto().equals("")) {
                cacheNameaccordingto.add(sp.getTaxonomicelement().getNameaccordingto());
            }
            add = true;
            for (Nameaccordingto j : cacheNameaccordingto) {
                if (j.getNameaccordingto().equals(sp.getTaxonomicelement().getNameaccordingto().getNameaccordingto()) || sp.getTaxonomicelement().getNameaccordingto().getNameaccordingto().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheNameaccordingto.add(sp.getTaxonomicelement().getNameaccordingto());
            }
            if (cacheNamepublishedin.isEmpty() && !sp.getTaxonomicelement().getNamepublishedin().getNamepublishedin().equals("")) {
                cacheNamepublishedin.add(sp.getTaxonomicelement().getNamepublishedin());
            }
            add = true;
            for (Namepublishedin j : cacheNamepublishedin) {
                if (j.getNamepublishedin().equals(sp.getTaxonomicelement().getNamepublishedin().getNamepublishedin()) || sp.getTaxonomicelement().getNamepublishedin().getNamepublishedin().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheNamepublishedin.add(sp.getTaxonomicelement().getNamepublishedin());
            }
            if (cacheNomenclaturalcode.isEmpty() && !sp.getTaxonomicelement().getNomenclaturalcode().getNomenclaturalcode().equals("")) {
                cacheNomenclaturalcode.add(sp.getTaxonomicelement().getNomenclaturalcode());
            }
            add = true;
            for (Nomenclaturalcode j : cacheNomenclaturalcode) {
                if (j.getNomenclaturalcode().equals(sp.getTaxonomicelement().getNomenclaturalcode().getNomenclaturalcode()) || sp.getTaxonomicelement().getNomenclaturalcode().getNomenclaturalcode().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheNomenclaturalcode.add(sp.getTaxonomicelement().getNomenclaturalcode());
            }
            if (cacheOrder1.isEmpty() && !sp.getTaxonomicelement().getOrder1().getOrder().equals("")) {
                cacheOrder1.add(sp.getTaxonomicelement().getOrder1());
            }
            add = true;
            for (Order1 j : cacheOrder1) {
                if (j.getOrder().equals(sp.getTaxonomicelement().getOrder1().getOrder()) || sp.getTaxonomicelement().getOrder1().getOrder().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheOrder1.add(sp.getTaxonomicelement().getOrder1());
            }
            if (cacheOriginalnameusage.isEmpty() && !sp.getTaxonomicelement().getOriginalnameusage().getOriginalnameusage().equals("")) {
                cacheOriginalnameusage.add(sp.getTaxonomicelement().getOriginalnameusage());
            }
            add = true;
            for (Originalnameusage j : cacheOriginalnameusage) {
                if (j.getOriginalnameusage().equals(sp.getTaxonomicelement().getOriginalnameusage().getOriginalnameusage()) || sp.getTaxonomicelement().getOriginalnameusage().getOriginalnameusage().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheOriginalnameusage.add(sp.getTaxonomicelement().getOriginalnameusage());
            }
            if (cacheParentnameusage.isEmpty() && !sp.getTaxonomicelement().getParentnameusage().getParentnameusage().equals("")) {
                cacheParentnameusage.add(sp.getTaxonomicelement().getParentnameusage());
            }
            add = true;
            for (Parentnameusage j : cacheParentnameusage) {
                if (j.getParentnameusage().equals(sp.getTaxonomicelement().getParentnameusage().getParentnameusage()) || sp.getTaxonomicelement().getParentnameusage().getParentnameusage().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheParentnameusage.add(sp.getTaxonomicelement().getParentnameusage());
            }
            if (cachePhylum.isEmpty() && !sp.getTaxonomicelement().getPhylum().getPhylum().equals("")) {
                cachePhylum.add(sp.getTaxonomicelement().getPhylum());
            }
            add = true;
            for (Phylum j : cachePhylum) {
                if (j.getPhylum().equals(sp.getTaxonomicelement().getPhylum().getPhylum()) || sp.getTaxonomicelement().getPhylum().getPhylum().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cachePhylum.add(sp.getTaxonomicelement().getPhylum());
            }
            if (cacheScientificname.isEmpty() && !sp.getTaxonomicelement().getScientificname().getScientificname().equals("")) {
                cacheScientificname.add(sp.getTaxonomicelement().getScientificname());
            }
            add = true;
            for (Scientificname j : cacheScientificname) {
                if (j.getScientificname().equals(sp.getTaxonomicelement().getScientificname().getScientificname()) || sp.getTaxonomicelement().getScientificname().getScientificname().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheScientificname.add(sp.getTaxonomicelement().getScientificname());
            }
            if (cacheScientificnameauthorship.isEmpty() && !sp.getTaxonomicelement().getScientificnameauthorship().getScientificnameauthorship().equals("")) {
                cacheScientificnameauthorship.add(sp.getTaxonomicelement().getScientificnameauthorship());
            }
            add = true;
            for (Scientificnameauthorship j : cacheScientificnameauthorship) {
                if (j.getScientificnameauthorship().equals(sp.getTaxonomicelement().getScientificnameauthorship().getScientificnameauthorship()) || sp.getTaxonomicelement().getScientificnameauthorship().getScientificnameauthorship().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheScientificnameauthorship.add(sp.getTaxonomicelement().getScientificnameauthorship());
            }
            if (cacheSpecificepithet.isEmpty() && !sp.getTaxonomicelement().getSpecificepithet().getSpecificepithet().equals("")) {
                cacheSpecificepithet.add(sp.getTaxonomicelement().getSpecificepithet());
            }
            add = true;
            for (Specificepithet j : cacheSpecificepithet) {
                if (j.getSpecificepithet().equals(sp.getTaxonomicelement().getSpecificepithet().getSpecificepithet()) || sp.getTaxonomicelement().getSpecificepithet().getSpecificepithet().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheSpecificepithet.add(sp.getTaxonomicelement().getSpecificepithet());
            }
            if (cacheSubgenus.isEmpty() && !sp.getTaxonomicelement().getSubgenus().getSubgenus().equals("")) {
                cacheSubgenus.add(sp.getTaxonomicelement().getSubgenus());
            }
            add = true;
            for (Subgenus j : cacheSubgenus) {
                if (j.getSubgenus().equals(sp.getTaxonomicelement().getSubgenus().getSubgenus()) || sp.getTaxonomicelement().getSubgenus().getSubgenus().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheSubgenus.add(sp.getTaxonomicelement().getSubgenus());
            }
            if (cacheTaxonconcept.isEmpty() && !sp.getTaxonomicelement().getTaxonconcept().getTaxonconcept().equals("")) {
                cacheTaxonconcept.add(sp.getTaxonomicelement().getTaxonconcept());
            }
            add = true;
            for (Taxonconcept j : cacheTaxonconcept) {
                if (j.getTaxonconcept().equals(sp.getTaxonomicelement().getTaxonconcept().getTaxonconcept()) || sp.getTaxonomicelement().getTaxonconcept().getTaxonconcept().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheTaxonconcept.add(sp.getTaxonomicelement().getTaxonconcept());
            }
            if (cacheTaxonomicstatus.isEmpty() && !sp.getTaxonomicelement().getTaxonomicstatus().getTaxonomicstatus().equals("")) {
                cacheTaxonomicstatus.add(sp.getTaxonomicelement().getTaxonomicstatus());
            }
            add = true;
            for (Taxonomicstatus j : cacheTaxonomicstatus) {
                if (j.getTaxonomicstatus().equals(sp.getTaxonomicelement().getTaxonomicstatus().getTaxonomicstatus()) || sp.getTaxonomicelement().getTaxonomicstatus().getTaxonomicstatus().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheTaxonomicstatus.add(sp.getTaxonomicelement().getTaxonomicstatus());
            }
            if (cacheTaxonrank.isEmpty() && !sp.getTaxonomicelement().getTaxonrank().getTaxonrank().equals("")) {
                cacheTaxonrank.add(sp.getTaxonomicelement().getTaxonrank());
            }
            add = true;
            for (Taxonrank j : cacheTaxonrank) {
                if (j.getTaxonrank().equals(sp.getTaxonomicelement().getTaxonrank().getTaxonrank()) || sp.getTaxonomicelement().getTaxonrank().getTaxonrank().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheTaxonrank.add(sp.getTaxonomicelement().getTaxonrank());
            }
            //ide
            if (cacheIdentificationqualifier.isEmpty() && !sp.getIdentificationelement().getIdentificationqualifier().getIdentificationqualifier().equals("")) {
                cacheIdentificationqualifier.add(sp.getIdentificationelement().getIdentificationqualifier());
            }

            for (Identificationqualifier j : cacheIdentificationqualifier) {
                if (j.getIdentificationqualifier().equals(sp.getIdentificationelement().getIdentificationqualifier().getIdentificationqualifier()) || sp.getIdentificationelement().getIdentificationqualifier().getIdentificationqualifier().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheIdentificationqualifier.add(sp.getIdentificationelement().getIdentificationqualifier());
            }
            //event
            if (cacheHabitat.isEmpty() && !sp.getEventelement().getHabitat().getHabitat().equals("")) {
                cacheHabitat.add(sp.getEventelement().getHabitat());
            }
            add = true;
            for (Habitat j : cacheHabitat) {
                if (j.getHabitat().equals(sp.getEventelement().getHabitat().getHabitat()) || sp.getEventelement().getHabitat().getHabitat().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheHabitat.add(sp.getEventelement().getHabitat());
            }
            if (cacheSamplingprotocol.isEmpty() && !sp.getEventelement().getSamplingprotocol().getSamplingprotocol().equals("")) {
                cacheSamplingprotocol.add(sp.getEventelement().getSamplingprotocol());
            }
            add = true;
            for (Samplingprotocol j : cacheSamplingprotocol) {
                if (j.getSamplingprotocol().equals(sp.getEventelement().getSamplingprotocol().getSamplingprotocol()) || sp.getEventelement().getSamplingprotocol().getSamplingprotocol().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheSamplingprotocol.add(sp.getEventelement().getSamplingprotocol());
            }
            //geo
            if (cacheGeoreferenceverificationstatus.isEmpty() && !sp.getGeospatialelement().getGeoreferenceverificationstatus().getGeoreferenceverificationstatus().equals("")) {
                cacheGeoreferenceverificationstatus.add(sp.getGeospatialelement().getGeoreferenceverificationstatus());
            }
            add = true;
            for (Georeferenceverificationstatus j : cacheGeoreferenceverificationstatus) {
                if (j.getGeoreferenceverificationstatus().equals(sp.getGeospatialelement().getGeoreferenceverificationstatus().getGeoreferenceverificationstatus()) || sp.getGeospatialelement().getGeoreferenceverificationstatus().getGeoreferenceverificationstatus().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheGeoreferenceverificationstatus.add(sp.getGeospatialelement().getGeoreferenceverificationstatus());
            }
            //loc
            if (cacheContinent.isEmpty() && !sp.getLocalityelement().getContinent().getContinent().equals("")) {
                cacheContinent.add(sp.getLocalityelement().getContinent());
            }
            add = true;
            for (Continent j : cacheContinent) {
                if (j.getContinent().equals(sp.getLocalityelement().getContinent().getContinent()) || sp.getLocalityelement().getContinent().getContinent().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheContinent.add(sp.getLocalityelement().getContinent());
            }
            if (cacheCountry.isEmpty() && !sp.getLocalityelement().getCountry().getCountry().equals("")) {
                cacheCountry.add(sp.getLocalityelement().getCountry());
            }
            add = true;
            for (Country j : cacheCountry) {
                if (j.getCountry().equals(sp.getLocalityelement().getCountry().getCountry()) || sp.getLocalityelement().getCountry().getCountry().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheCountry.add(sp.getLocalityelement().getCountry());
            }
            if (cacheCounty.isEmpty() && !sp.getLocalityelement().getCounty().getCounty().equals("")) {
                cacheCounty.add(sp.getLocalityelement().getCounty());
            }
            add = true;
            for (County j : cacheCounty) {
                if (j.getCounty().equals(sp.getLocalityelement().getCounty().getCounty()) || sp.getLocalityelement().getCounty().getCounty().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheCounty.add(sp.getLocalityelement().getCounty());
            }
            if (cacheIsland.isEmpty() && !sp.getLocalityelement().getIsland().getIsland().equals("")) {
                cacheIsland.add(sp.getLocalityelement().getIsland());
            }
            add = true;
            for (Island j : cacheIsland) {
                if (j.getIsland().equals(sp.getLocalityelement().getIsland().getIsland()) || sp.getLocalityelement().getIsland().getIsland().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheIsland.add(sp.getLocalityelement().getIsland());
            }
            if (cacheIslandgroup.isEmpty() && !sp.getLocalityelement().getIslandgroup().getIslandgroup().equals("")) {
                cacheIslandgroup.add(sp.getLocalityelement().getIslandgroup());
            }
            add = true;
            for (Islandgroup j : cacheIslandgroup) {
                if (j.getIslandgroup().equals(sp.getLocalityelement().getIslandgroup().getIslandgroup()) || sp.getLocalityelement().getIslandgroup().getIslandgroup().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheIslandgroup.add(sp.getLocalityelement().getIslandgroup());
            }
            if (cacheLocality.isEmpty() && !sp.getLocalityelement().getLocality().getLocality().equals("")) {
                cacheLocality.add(sp.getLocalityelement().getLocality());
            }
            add = true;
            for (Locality j : cacheLocality) {
                if (j.getLocality().equals(sp.getLocalityelement().getLocality().getLocality()) || sp.getLocalityelement().getLocality().getLocality().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheLocality.add(sp.getLocalityelement().getLocality());
            }
            if (cacheMunicipality.isEmpty() && !sp.getLocalityelement().getMunicipality().getMunicipality().equals("")) {
                cacheMunicipality.add(sp.getLocalityelement().getMunicipality());
            }
            add = true;
            for (Municipality j : cacheMunicipality) {
                if (j.getMunicipality().equals(sp.getLocalityelement().getMunicipality().getMunicipality()) || sp.getLocalityelement().getMunicipality().getMunicipality().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheMunicipality.add(sp.getLocalityelement().getMunicipality());
            }
            if (cacheStateprovince.isEmpty() && !sp.getLocalityelement().getStateprovince().getStateprovince().equals("")) {
                cacheStateprovince.add(sp.getLocalityelement().getStateprovince());
            }
            add = true;
            for (Stateprovince j : cacheStateprovince) {
                if (j.getStateprovince().equals(sp.getLocalityelement().getStateprovince().getStateprovince()) || sp.getLocalityelement().getStateprovince().getStateprovince().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheStateprovince.add(sp.getLocalityelement().getStateprovince());
            }
            if (cacheWaterbody.isEmpty() && !sp.getLocalityelement().getWaterbody().getWaterbody().equals("")) {
                cacheWaterbody.add(sp.getLocalityelement().getWaterbody());
            }
            add = true;
            for (Waterbody j : cacheWaterbody) {
                if (j.getWaterbody().equals(sp.getLocalityelement().getWaterbody().getWaterbody()) || sp.getLocalityelement().getWaterbody().getWaterbody().equals("")) {
                    add = false;
                    break;
                }
            }
            if (add == true) {
                cacheWaterbody.add(sp.getLocalityelement().getWaterbody());
            }

            //NN
            if (cacheDynamicpropertyNN.isEmpty() && !sp.getRecordlevelelement().getDynamicpropertyList().isEmpty()) {
                cacheDynamicpropertyNN.add(sp.getRecordlevelelement().getDynamicpropertyList().get(0));
            }
            for (Dynamicproperty i : sp.getRecordlevelelement().getDynamicpropertyList()) {
                add = true;
                for (Dynamicproperty j : cacheDynamicpropertyNN) {
                    if (j.getDynamicproperty().equals(i.getDynamicproperty())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheDynamicpropertyNN.add(i);
                }
            }

            if (cacheAssociatedsequenceNN.isEmpty() && !sp.getOccurrenceelement().getAssociatedsequenceList().isEmpty()) {
                cacheAssociatedsequenceNN.add(sp.getOccurrenceelement().getAssociatedsequenceList().get(0));
            }
            for (Associatedsequence i : sp.getOccurrenceelement().getAssociatedsequenceList()) {
                add = true;
                for (Associatedsequence j : cacheAssociatedsequenceNN) {
                    if (j.getAssociatedsequence().equals(i.getAssociatedsequence())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheAssociatedsequenceNN.add(i);
                }
            }

            if (cacheIndividualNN.isEmpty() && !sp.getOccurrenceelement().getIndividualList().isEmpty()) {
                cacheIndividualNN.add(sp.getOccurrenceelement().getIndividualList().get(0));
            }
            for (Individual i : sp.getOccurrenceelement().getIndividualList()) {
                add = true;
                for (Individual j : cacheIndividualNN) {
                    if (j.getIndividual().equals(i.getIndividual())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheIndividualNN.add(i);
                }
            }

            if (cachePreparationNN.isEmpty() && !sp.getOccurrenceelement().getPreparationList().isEmpty()) {
                cachePreparationNN.add(sp.getOccurrenceelement().getPreparationList().get(0));
            }
            for (Preparation i : sp.getOccurrenceelement().getPreparationList()) {
                add = true;
                for (Preparation j : cachePreparationNN) {
                    if (j.getPreparation().equals(i.getPreparation())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cachePreparationNN.add(i);
                }
            }

            if (cacheRecordedbyNN.isEmpty() && !sp.getOccurrenceelement().getRecordedbyList().isEmpty()) {
                cacheRecordedbyNN.add(sp.getOccurrenceelement().getRecordedbyList().get(0));
            }
            for (Recordedby i : sp.getOccurrenceelement().getRecordedbyList()) {
                add = true;
                for (Recordedby j : cacheRecordedbyNN) {
                    if (j.getRecordedby().equals(i.getRecordedby())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheRecordedbyNN.add(i);
                }
            }

            if (cacheIdentifiedbyNN.isEmpty() && !sp.getIdentificationelement().getIdentifiedbyList().isEmpty()) {
                cacheIdentifiedbyNN.add(sp.getIdentificationelement().getIdentifiedbyList().get(0));
            }
            for (Identifiedby i : sp.getIdentificationelement().getIdentifiedbyList()) {
                add = true;
                for (Identifiedby j : cacheIdentifiedbyNN) {
                    if (j.getIdentifiedby().equals(i.getIdentifiedby())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheIdentifiedbyNN.add(i);
                }
            }

            if (cacheTypestatusNN.isEmpty() && !sp.getIdentificationelement().getTypestatusList().isEmpty()) {
                cacheTypestatusNN.add(sp.getIdentificationelement().getTypestatusList().get(0));
            }
            for (Typestatus i : sp.getIdentificationelement().getTypestatusList()) {
                add = true;
                for (Typestatus j : cacheTypestatusNN) {
                    if (j.getTypestatus().equals(i.getTypestatus())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheTypestatusNN.add(i);
                }
            }

            if (cacheGeoreferencedbyNN.isEmpty() && !sp.getLocalityelement().getGeoreferencedbyList().isEmpty()) {
                cacheGeoreferencedbyNN.add(sp.getLocalityelement().getGeoreferencedbyList().get(0));
            }
            for (Georeferencedby i : sp.getLocalityelement().getGeoreferencedbyList()) {
                add = true;
                for (Georeferencedby j : cacheGeoreferencedbyNN) {
                    if (j.getGeoreferencedby().equals(i.getGeoreferencedby())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheGeoreferencedbyNN.add(i);
                }
            }

            if (cacheGeoreferencesourceNN.isEmpty() && !sp.getLocalityelement().getGeoreferencesourceList().isEmpty()) {
                cacheGeoreferencesourceNN.add(sp.getLocalityelement().getGeoreferencesourceList().get(0));
            }
            for (Georeferencesource i : sp.getLocalityelement().getGeoreferencesourceList()) {
                add = true;
                for (Georeferencesource j : cacheGeoreferencesourceNN) {
                    if (j.getGeoreferencesource().equals(i.getGeoreferencesource())) {
                        add = false;
                        break;
                    }
                }
                if (add == true) {
                    cacheGeoreferencesourceNN.add(i);
                }
            }




        }
//        System.out.println("waterb");
//        for (Waterbody j : cacheWaterbody) {
//            System.out.println(j.getWaterbody());
//            System.out.println(j.getIdwaterbody());
//        }
//        System.out.println("------");
//        for (Dynamicproperty j : cacheDynamicpropertyNN) {
//            System.out.println(j.getIddynamicproperty());
//            System.out.println(j.getDynamicproperty());
//            System.out.println("------");
//        }
        if (!cacheBasisofrecord.isEmpty()) {
            cacheBasisofrecord = saveTables.saveBasisofrecord(cacheBasisofrecord);
        }
        if (!cacheInstitutioncode.isEmpty()) {
            cacheInstitutioncode = saveTables.saveInstitutioncode(cacheInstitutioncode);
        }
        if (!cacheCollectioncode.isEmpty()) {
            cacheCollectioncode = saveTables.saveCollectioncode(cacheCollectioncode);
        }
        if (!cacheOwnerinstitution.isEmpty()) {
            cacheOwnerinstitution = saveTables.saveOwnerinstitution(cacheOwnerinstitution);
        }
        if (!cacheDataset.isEmpty()) {
            cacheDataset = saveTables.saveDataset(cacheDataset);
        }
        if (!cacheType.isEmpty()) {
            cacheType = saveTables.saveType(cacheType);
        }
        if (!cacheBehavior.isEmpty()) {
            cacheBehavior = saveTables.saveBehavior(cacheBehavior);
        }
        if (!cacheDisposition.isEmpty()) {
            cacheDisposition = saveTables.saveDisposition(cacheDisposition);
        }
        if (!cacheEstablishmentmean.isEmpty()) {
            cacheEstablishmentmean = saveTables.saveEstablishmentmean(cacheEstablishmentmean);
        }
        if (!cacheLifestage.isEmpty()) {
            cacheLifestage = saveTables.saveLifestage(cacheLifestage);
        }
        if (!cacheReproductivecondition.isEmpty()) {
            cacheReproductivecondition = saveTables.saveReproductivecondition(cacheReproductivecondition);
        }
        if (!cacheSex.isEmpty()) {
            cacheSex = saveTables.saveSex(cacheSex);
        }
        if (!cacheAcceptednameusage.isEmpty()) {
            cacheAcceptednameusage = saveTables.saveAcceptednameusage(cacheAcceptednameusage);
        }
        if (!cacheClass.isEmpty()) {
            cacheClass = saveTables.saveClass(cacheClass);
        }
        if (!cacheFamily.isEmpty()) {
            cacheFamily = saveTables.saveFamily(cacheFamily);
        }
        if (!cacheGenus.isEmpty()) {
            cacheGenus = saveTables.saveGenus(cacheGenus);
        }
        if (!cacheInfraspecificepithet.isEmpty()) {
            cacheInfraspecificepithet = saveTables.saveInfraspecificepithet(cacheInfraspecificepithet);
        }
        if (!cacheKingdom.isEmpty()) {
            cacheKingdom = saveTables.saveKingdom(cacheKingdom);
        }
        if (!cacheNameaccordingto.isEmpty()) {
            cacheNameaccordingto = saveTables.saveNameaccordingto(cacheNameaccordingto);
        }
        if (!cacheNamepublishedin.isEmpty()) {
            cacheNamepublishedin = saveTables.saveNamepublishedin(cacheNamepublishedin);
        }
        if (!cacheNomenclaturalcode.isEmpty()) {
            cacheNomenclaturalcode = saveTables.saveNomenclaturalcode(cacheNomenclaturalcode);
        }
        if (!cacheOrder1.isEmpty()) {
            cacheOrder1 = saveTables.saveOrder1(cacheOrder1);
        }
        if (!cacheOriginalnameusage.isEmpty()) {
            cacheOriginalnameusage = saveTables.saveOriginalnameusage(cacheOriginalnameusage);
        }
        if (!cacheParentnameusage.isEmpty()) {
            cacheParentnameusage = saveTables.saveParentnameusage(cacheParentnameusage);
        }
        if (!cachePhylum.isEmpty()) {
            cachePhylum = saveTables.savePhylum(cachePhylum);
        }
        if (!cacheScientificname.isEmpty()) {
            cacheScientificname = saveTables.saveScientificname(cacheScientificname);
        }
        if (!cacheScientificnameauthorship.isEmpty()) {
            cacheScientificnameauthorship = saveTables.saveScientificnameauthorship(cacheScientificnameauthorship);
        }
        if (!cacheSpecificepithet.isEmpty()) {
            cacheSpecificepithet = saveTables.saveSpecificepithet(cacheSpecificepithet);
        }
        if (!cacheSubgenus.isEmpty()) {
            cacheSubgenus = saveTables.saveSubgenus(cacheSubgenus);
        }
        if (!cacheTaxonconcept.isEmpty()) {
            cacheTaxonconcept = saveTables.saveTaxonconcept(cacheTaxonconcept);
        }
        if (!cacheTaxonomicstatus.isEmpty()) {
            cacheTaxonomicstatus = saveTables.saveTaxonomicstatus(cacheTaxonomicstatus);
        }
        if (!cacheTaxonrank.isEmpty()) {
            cacheTaxonrank = saveTables.saveTaxonrank(cacheTaxonrank);
        }
        if (!cacheIdentificationqualifier.isEmpty()) {
            cacheIdentificationqualifier = saveTables.saveIdentificationqualifier(cacheIdentificationqualifier);
        }
        if (!cacheHabitat.isEmpty()) {
            cacheHabitat = saveTables.saveHabitat(cacheHabitat);
        }
        if (!cacheSamplingprotocol.isEmpty()) {
            cacheSamplingprotocol = saveTables.saveSamplingprotocol(cacheSamplingprotocol);
        }
        if (!cacheGeoreferenceverificationstatus.isEmpty()) {
            cacheGeoreferenceverificationstatus = saveTables.saveGeoreferenceverificationstatus(cacheGeoreferenceverificationstatus);
        }
        if (!cacheContinent.isEmpty()) {
            cacheContinent = saveTables.saveContinent(cacheContinent);
        }
        if (!cacheCountry.isEmpty()) {
            cacheCountry = saveTables.saveCountry(cacheCountry);
        }
        if (!cacheCounty.isEmpty()) {
            cacheCounty = saveTables.saveCounty(cacheCounty);
        }
        if (!cacheIsland.isEmpty()) {
            cacheIsland = saveTables.saveIsland(cacheIsland);
        }
        if (!cacheIslandgroup.isEmpty()) {
            cacheIslandgroup = saveTables.saveIslandgroup(cacheIslandgroup);
        }
        if (!cacheLocality.isEmpty()) {
            cacheLocality = saveTables.saveLocality(cacheLocality);
        }
        if (!cacheMunicipality.isEmpty()) {
            cacheMunicipality = saveTables.saveMunicipality(cacheMunicipality);
        }
        if (!cacheStateprovince.isEmpty()) {
            cacheStateprovince = saveTables.saveStateprovince(cacheStateprovince);
        }
        if (!cacheWaterbody.isEmpty()) {
            cacheWaterbody = saveTables.saveWaterbody(cacheWaterbody);
        }

        if (!cacheDynamicpropertyNN.isEmpty()) {
            cacheDynamicpropertyNN = saveTables.saveDynamicpropertyNN(cacheDynamicpropertyNN);
        }
        if (!cacheGeoreferencesourceNN.isEmpty()) {
            cacheGeoreferencesourceNN = saveTables.saveGeoreferencesourceNN(cacheGeoreferencesourceNN);
        }
        if (!cacheAssociatedsequenceNN.isEmpty()) {
            cacheAssociatedsequenceNN = saveTables.saveAssociatedsequenceNN(cacheAssociatedsequenceNN);
        }
        if (!cacheIndividualNN.isEmpty()) {
            cacheIndividualNN = saveTables.saveIndividualNN(cacheIndividualNN);
        }
        if (!cachePreparationNN.isEmpty()) {
            cachePreparationNN = saveTables.savePreparationNN(cachePreparationNN);
        }
        if (!cacheRecordedbyNN.isEmpty()) {
            cacheRecordedbyNN = saveTables.saveRecordedbyNN(cacheRecordedbyNN);
        }
        if (!cacheIdentifiedbyNN.isEmpty()) {
            cacheIdentifiedbyNN = saveTables.saveIdentifiedbyNN(cacheIdentifiedbyNN);
        }
        if (!cacheTypestatusNN.isEmpty()) {
            cacheTypestatusNN = saveTables.saveTypestatusNN(cacheTypestatusNN);
        }
        if (!cacheGeoreferencedbyNN.isEmpty()) {
            cacheGeoreferencedbyNN = saveTables.saveGeoreferencedbyNN(cacheGeoreferencedbyNN);
        }
    }

    

    

    public Specimen save(List<Specimen> listSpecimen) {
        StringBuffer sql = new StringBuffer();
        int count = 0;
        int grav = 0;

        for (Specimen sp : listSpecimen) {
            Map<String, Integer> m = new HashMap<String, Integer>();
//            //seta GUI
//            sp.getRecordlevelelement().setGlobaluniqueidentifier(sp.getRecordlevelelement().getInstitutioncode().getInstitutioncode()
//                    + ":" + sp.getRecordlevelelement().getCollectioncode().getCollectioncode()
//                    + ":" + sp.getOccurrenceelement().getCatalognumber());
            m = findIdSpecimenAndElementsByGUI(sp);
            sp.setIdspecimen(m.get("idspecimen"));
            if (validation(sp)) {
                if (sp.getIdspecimen() == null) {
                    //create
                    sp = fillDependency(sp);
                    sql = sqlGenerate.createSQL(sp, sql);
                    count++;
                    if (count >= 100) {
                        //System.out.println(sql.toString());
                        executeUpdates(sql.toString());
                        //System.out.println("gravou " + grav++);
                        count = 0;
                        sql = new StringBuffer();
                    }

                } else {
                    //edit
                    sp = fillDependency(sp);

                    sp.getRecordlevelelement().setIdrecordlevelelement(m.get("idrecordlevelelement"));
                    sp.getTaxonomicelement().setIdtaxonomicelement(m.get("idtaxonomicelement"));
                    sp.getOccurrenceelement().setIdoccurrenceelement(m.get("idoccurrenceelement"));
                    sp.getLocalityelement().setIdlocalityelement(m.get("idlocalityelement"));
                    sp.getEventelement().setIdeventelement(m.get("ideventelement"));
                    sp.getIdentificationelement().setIdidentificationelement(m.get("ididentificationelement"));
                    sp.getGeospatialelement().setIdgeospatialelement(m.get("idgeospatialelement"));

                    sql = sqlGenerate.updateSQL(sp, sql);

                    count++;

                    if (count >= 100) {
                        executeUpdates(sql.toString());
                        //System.out.println("gravou "+grav++);
                        count = 0;
                        sql = new StringBuffer();
                    }
                }
            }
        }
        if (!sql.toString().equals("")) {
            System.out.println(sql.toString());
            executeUpdates(sql.toString());
            //System.out.println("gravou "+grav++);
        }

        return null;
    }

    public void delete (List<Specimen> listSpecimen){
        StringBuffer sql = new StringBuffer();
        Map<String, Integer> m = new HashMap<String, Integer>();
        int count = 0;



        for (Specimen sp : listSpecimen) {
            m = findIdSpecimenAndElementsByGUI(sp);
            sp.setIdspecimen(m.get("idspecimen"));

            if (sp.getIdspecimen() != null) {

                sp.getRecordlevelelement().setIdrecordlevelelement(m.get("idrecordlevelelement"));
                sp.getTaxonomicelement().setIdtaxonomicelement(m.get("idtaxonomicelement"));
                sp.getOccurrenceelement().setIdoccurrenceelement(m.get("idoccurrenceelement"));
                sp.getLocalityelement().setIdlocalityelement(m.get("idlocalityelement"));
                sp.getEventelement().setIdeventelement(m.get("ideventelement"));
                sp.getIdentificationelement().setIdidentificationelement(m.get("ididentificationelement"));
                sp.getGeospatialelement().setIdgeospatialelement(m.get("idgeospatialelement"));

                sql = sqlGenerate.deleteSQL(sp, sql);
                count++;
                if (count >= 100) {
                    //System.out.println(sql.toString());
                    executeUpdates(sql.toString());
                    count = 0;
                    sql = new StringBuffer();
                }
            }
        }
        if (!sql.toString().equals("")) {
            //System.out.println(sql.toString());
            executeUpdates(sql.toString());
        }
        
    }

    public boolean validation(Specimen sp) {
        if (sp.getRecordlevelelement().getInstitutioncode().getInstitutioncode().equals("")) {

            return false;
        }
        if (sp.getRecordlevelelement().getCollectioncode().getCollectioncode().equals("")) {
            return false;
        }
        if (sp.getOccurrenceelement().getCatalognumber().equals("")) {
            return false;
        }
        return true;
    }

    public Specimen fillDependency(Specimen sp) {

        sp.setRecordlevelelement(fillRecordLevel(sp.getRecordlevelelement()));
        sp.setOccurrenceelement(fillOccurrence(sp.getOccurrenceelement()));
        sp.setTaxonomicelement(fillTaxonomic(sp.getTaxonomicelement()));
        sp.setIdentificationelement(fillIdentification(sp.getIdentificationelement()));
        sp.setEventelement(fillEvent(sp.getEventelement()));
        sp.setGeospatialelement(fillGeospatial(sp.getGeospatialelement()));
        sp.setLocalityelement(fillLocality(sp.getLocalityelement()));

        return sp;
    }

    public Recordlevelelement fillRecordLevel(Recordlevelelement r) {

        r.setModified(new java.util.Date());

        for (Basisofrecord i : cacheBasisofrecord) {
            if (r.getBasisofrecord().getBasisofrecord().equals(i.getBasisofrecord())) {
                r.setBasisofrecord(i);
                break;
            }
        }

        for (Institutioncode i : cacheInstitutioncode) {
            if (r.getInstitutioncode().getInstitutioncode().equals(i.getInstitutioncode())) {
                r.setInstitutioncode(i);
                break;
            }
        }

        for (Collectioncode i : cacheCollectioncode) {
            if (r.getCollectioncode().getCollectioncode().equals(i.getCollectioncode())) {
                r.setCollectioncode(i);
                break;
            }
        }

        for (Dataset i : cacheDataset) {
            if (r.getDataset().getDataset().equals(i.getDataset())) {
                r.setDataset(i);
                break;
            }
        }

        r.setLanguage(null); //nao tem na panilha
        for (Ownerinstitution i : cacheOwnerinstitution) {
            if (r.getOwnerinstitution().getOwnerinstitution().equals(i.getOwnerinstitution())) {
                r.setOwnerinstitution(i);
                break;
            }
        }

        for (Type i : cacheType) {
            if (r.getType().getType().equals(i.getType())) {
                r.setType(i);
                break;
            }
        }

        // Lógica para NxN
        for (Dynamicproperty aux : r.getDynamicpropertyList()) {
            if (!aux.getDynamicproperty().equals("")) {
                for (Dynamicproperty j : cacheDynamicpropertyNN) {
                    if (j.getDynamicproperty().equals(aux.getDynamicproperty())) {
                        aux.setIddynamicproperty(j.getIddynamicproperty());
                        break;
                    }
                }
            }
        }

        return r;
    }

    public Occurrenceelement fillOccurrence(Occurrenceelement o) {
        for (Behavior i : cacheBehavior) {
            if (o.getBehavior().getBehavior().equals(i.getBehavior())) {
                o.setBehavior(i);
                break;
            }
        }
        //o.setBehavior(o.getBehavior().getBehavior().equals("") ? null : new BehaviorJpaController().save(o.getBehavior()));
        for (Disposition i : cacheDisposition) {
            if (o.getDisposition().getDisposition().equals(i.getDisposition())) {
                o.setDisposition(i);
                break;
            }
        }
        //o.setDisposition(o.getDisposition().getDisposition().equals("") ? null : new DispositionJpaController().save(o.getDisposition()));
        for (Establishmentmean i : cacheEstablishmentmean) {
            if (o.getEstablishmentmean().getEstablishmentmean().equals(i.getEstablishmentmean())) {
                o.setEstablishmentmean(i);
                break;
            }
        }
        //o.setEstablishmentmean(o.getEstablishmentmean().getEstablishmentmean().equals("") ? null : new EstablishmentmeanJpaController().save(o.getEstablishmentmean()));
        for (Lifestage i : cacheLifestage) {
            if (o.getLifestage().getLifestage().equals(i.getLifestage())) {
                o.setLifestage(i);
                break;
            }
        }
        //o.setLifestage(o.getLifestage().getLifestage().equals("") ? null : new LifestageJpaController().save(o.getLifestage()));
        for (Reproductivecondition i : cacheReproductivecondition) {
            if (o.getReproductivecondition().getReproductivecondition().equals(i.getReproductivecondition())) {
                o.setReproductivecondition(i);
                break;
            }
        }
        //o.setReproductivecondition(o.getReproductivecondition().getReproductivecondition().equals("") ? null : new ReproductiveconditionJpaController().save(o.getReproductivecondition()));
        for (Sex i : cacheSex) {
            if (o.getSex().getSex().equals(i.getSex())) {
                o.setSex(i);
                break;
            }
        }
        //o.setSex(o.getSex().getSex().equals("") ? null : new SexJpaController().save(o.getSex()));

        for (Associatedsequence aux : o.getAssociatedsequenceList()) {
            if (!aux.getAssociatedsequence().equals("")) {
                for (Associatedsequence j : cacheAssociatedsequenceNN) {
                    if (j.getAssociatedsequence().equals(aux.getAssociatedsequence())) {
                        aux.setIdassociatedsequence(j.getIdassociatedsequence());
                        break;
                    }
                }
            }
        }

        for (Individual aux : o.getIndividualList()) {
            if (!aux.getIndividual().equals("")) {
                for (Individual j : cacheIndividualNN) {
                    if (j.getIndividual().equals(aux.getIndividual())) {
                        aux.setIdindividual(j.getIdindividual());
                        break;
                    }
                }
            }
        }

        for (Preparation aux : o.getPreparationList()) {
            if (!aux.getPreparation().equals("")) {
                for (Preparation j : cachePreparationNN) {
                    if (j.getPreparation().equals(aux.getPreparation())) {
                        aux.setIdpreparation(j.getIdpreparation());
                        break;
                    }
                }
            }
        }

        for (Recordedby aux : o.getRecordedbyList()) {
            if (!aux.getRecordedby().equals("")) {
                for (Recordedby j : cacheRecordedbyNN) {
                    if (j.getRecordedby().equals(aux.getRecordedby())) {
                        aux.setIdrecordedby(j.getIdrecordedby());
                        break;
                    }
                }
            }
        }

        return o;
    }

    public Taxonomicelement fillTaxonomic(Taxonomicelement t) {
        for (Acceptednameusage i : cacheAcceptednameusage) {
            if (t.getAcceptednameusage().getAcceptednameusage().equals(i.getAcceptednameusage())) {
                t.setAcceptednameusage(i);
                break;
            }
        }
        //t.setAcceptednameusage(t.getAcceptednameusage().getAcceptednameusage().equals("") ? null : new AcceptednameusageJpaController().save(t.getAcceptednameusage()));
        for (Class i : cacheClass) {
            if (t.getClass1().getClass1().equals(i.getClass1())) {
                t.setClass1(i);
                break;
            }
        }
        //t.setClass1(t.getClass1().getClass1().equals("") ? null : new ClassJpaController().save(t.getClass1()));
        for (Family i : cacheFamily) {
            if (t.getFamily().getFamily().equals(i.getFamily())) {
                t.setFamily(i);
                break;
            }
        }
        //t.setFamily(t.getFamily().getFamily().equals("") ? null : new FamilyJpaController().save(t.getFamily()));
        for (Genus i : cacheGenus) {
            if (t.getGenus().getGenus().equals(i.getGenus())) {
                t.setGenus(i);
                break;
            }
        }
        //t.setGenus(t.getGenus().getGenus().equals("") ? null : new GenusJpaController().save(t.getGenus()));
        for (Infraspecificepithet i : cacheInfraspecificepithet) {
            if (t.getInfraspecificepithet().getInfraspecificepithet().equals(i.getInfraspecificepithet())) {
                t.setInfraspecificepithet(i);
                break;
            }
        }
        //t.setInfraspecificepithet(t.getInfraspecificepithet().getInfraspecificepithet().equals("") ? null : new InfraspecificepithetJpaController().save(t.getInfraspecificepithet()));
        for (Kingdom i : cacheKingdom) {
            if (t.getKingdom().getKingdom().equals(i.getKingdom())) {
                t.setKingdom(i);
                break;
            }
        }
        //t.setKingdom(t.getKingdom().getKingdom().equals("") ? null : new KingdomJpaController().save(t.getKingdom()));
        for (Nameaccordingto i : cacheNameaccordingto) {
            if (t.getNameaccordingto().getNameaccordingto().equals(i.getNameaccordingto())) {
                t.setNameaccordingto(i);
                break;
            }
        }
        //t.setNameaccordingto(t.getNameaccordingto().getNameaccordingto().equals("") ? null : new NameaccordingtoJpaController().save(t.getNameaccordingto()));
        for (Namepublishedin i : cacheNamepublishedin) {
            if (t.getNamepublishedin().getNamepublishedin().equals(i.getNamepublishedin())) {
                t.setNamepublishedin(i);
                break;
            }
        }
        //t.setNamepublishedin(t.getNamepublishedin().getNamepublishedin().equals("") ? null : new NamepublishedinJpaController().save(t.getNamepublishedin()));
        for (Nomenclaturalcode i : cacheNomenclaturalcode) {
            if (t.getNomenclaturalcode().getNomenclaturalcode().equals(i.getNomenclaturalcode())) {
                t.setNomenclaturalcode(i);
                break;
            }
        }
        //t.setNomenclaturalcode(t.getNomenclaturalcode().getNomenclaturalcode().equals("") ? null : new NomenclaturalcodeJpaController().save(t.getNomenclaturalcode()));
        for (Order1 i : cacheOrder1) {
            if (t.getOrder1().getOrder().equals(i.getOrder())) {
                t.setOrder1(i);
                break;
            }
        }
        //t.setOrder1(t.getOrder1().getOrder().equals("") ? null : new Order1JpaController().save(t.getOrder1()));
        for (Originalnameusage i : cacheOriginalnameusage) {
            if (t.getOriginalnameusage().getOriginalnameusage().equals(i.getOriginalnameusage())) {
                t.setOriginalnameusage(i);
                break;
            }
        }
        //t.setOriginalnameusage(t.getOriginalnameusage().getOriginalnameusage().equals("") ? null : new OriginalnameusageJpaController().save(t.getOriginalnameusage()));
        for (Parentnameusage i : cacheParentnameusage) {
            if (t.getParentnameusage().getParentnameusage().equals(i.getParentnameusage())) {
                t.setParentnameusage(i);
                break;
            }
        }
        //t.setParentnameusage(t.getParentnameusage().getParentnameusage().equals("") ? null : new ParentnameusageJpaController().save(t.getParentnameusage()));
        for (Phylum i : cachePhylum) {
            if (t.getPhylum().getPhylum().equals(i.getPhylum())) {
                t.setPhylum(i);
                break;
            }
        }
        //t.setPhylum(t.getPhylum().getPhylum().equals("") ? null : new PhylumJpaController().save(t.getPhylum()));
        for (Scientificname i : cacheScientificname) {
            if (t.getScientificname().getScientificname().equals(i.getScientificname())) {
                t.setScientificname(i);
                break;
            }
        }
        //t.setScientificname(t.getScientificname().getScientificname().equals("") ? null : new ScientificnameJpaController().save(t.getScientificname()));
        for (Scientificnameauthorship i : cacheScientificnameauthorship) {
            if (t.getScientificnameauthorship().getScientificnameauthorship().equals(i.getScientificnameauthorship())) {
                t.setScientificnameauthorship(i);
                break;
            }
        }
        //t.setScientificnameauthorship(t.getScientificnameauthorship().getScientificnameauthorship().equals("") ? null : new ScientificnameauthorshipJpaController().save(t.getScientificnameauthorship()));
        for (Specificepithet i : cacheSpecificepithet) {
            if (t.getSpecificepithet().getSpecificepithet().equals(i.getSpecificepithet())) {
                t.setSpecificepithet(i);
                break;
            }
        }
        //t.setSpecificepithet(t.getSpecificepithet().getSpecificepithet().equals("") ? null : new SpecificepithetJpaController().save(t.getSpecificepithet()));
        for (Subgenus i : cacheSubgenus) {
            if (t.getSubgenus().getSubgenus().equals(i.getSubgenus())) {
                t.setSubgenus(i);
                break;
            }
        }
        //t.setSubgenus(t.getSubgenus().getSubgenus().equals("") ? null : new SubgenusJpaController().save(t.getSubgenus()));
        for (Taxonconcept i : cacheTaxonconcept) {
            if (t.getTaxonconcept().getTaxonconcept().equals(i.getTaxonconcept())) {
                t.setTaxonconcept(i);
                break;
            }
        }
        //t.setTaxonconcept(t.getTaxonconcept().getTaxonconcept().equals("") ? null : new TaxonconceptJpaController().save(t.getTaxonconcept()));
        for (Taxonomicstatus i : cacheTaxonomicstatus) {
            if (t.getTaxonomicstatus().getTaxonomicstatus().equals(i.getTaxonomicstatus())) {
                t.setTaxonomicstatus(i);
                break;
            }
        }
        //t.setTaxonomicstatus(t.getTaxonomicstatus().getTaxonomicstatus().equals("") ? null : new TaxonomicstatusJpaController().save(t.getTaxonomicstatus()));
        for (Taxonrank i : cacheTaxonrank) {
            if (t.getTaxonrank().getTaxonrank().equals(i.getTaxonrank())) {
                t.setTaxonrank(i);
                break;
            }
        }
        //t.setTaxonrank(t.getTaxonrank().getTaxonrank().equals("") ? null : new TaxonrankJpaController().save(t.getTaxonrank()));

        return t;
    }

    public Identificationelement fillIdentification(Identificationelement i) {
        for (Identificationqualifier i_aux : cacheIdentificationqualifier) {
            if (i.getIdentificationqualifier().getIdentificationqualifier().equals(i_aux.getIdentificationqualifier())) {
                i.setIdentificationqualifier(i_aux);
                break;
            }
        }
        //i.setIdentificationqualifier(i.getIdentificationqualifier().getIdentificationqualifier().equals("") ? null : new IdentificationqualifierJpaController().save(i.getIdentificationqualifier()));

        for (Identifiedby aux : i.getIdentifiedbyList()) {
            if (!aux.getIdentifiedby().equals("")) {
                for (Identifiedby j : cacheIdentifiedbyNN) {
                    if (j.getIdentifiedby().equals(aux.getIdentifiedby())) {
                        aux.setIdidentifiedby(j.getIdidentifiedby());
                        break;
                    }
                }
            }
        }

        for (Typestatus aux : i.getTypestatusList()) {
            if (!aux.getTypestatus().equals("")) {
                for (Typestatus j : cacheTypestatusNN) {
                    if (j.getTypestatus().equals(aux.getTypestatus())) {
                        aux.setIdtypestatus(j.getIdtypestatus());
                        break;
                    }
                }
            }
        }

        return i;
    }

    public Eventelement fillEvent(Eventelement e) {
        for (Habitat i : cacheHabitat) {
            if (e.getHabitat().getHabitat().equals(i.getHabitat())) {
                e.setHabitat(i);
                break;
            }
        }
        //e.setHabitat(e.getHabitat().getHabitat().equals("") ? null : new HabitatJpaController().save(e.getHabitat()));
        for (Samplingprotocol i : cacheSamplingprotocol) {
            if (e.getSamplingprotocol().getSamplingprotocol().equals(i.getSamplingprotocol())) {
                e.setSamplingprotocol(i);
                break;
            }
        }
        //e.setSamplingprotocol(e.getSamplingprotocol().getSamplingprotocol().equals("") ? null : new SamplingprotocolJpaController().save(e.getSamplingprotocol()));

        return e;
    }

    public Geospatialelement fillGeospatial(Geospatialelement g) {
        for (Georeferenceverificationstatus i : cacheGeoreferenceverificationstatus) {
            if (g.getGeoreferenceverificationstatus().getGeoreferenceverificationstatus().equals(i.getGeoreferenceverificationstatus())) {
                g.setGeoreferenceverificationstatus(i);
                break;
            }
        }
        //g.setGeoreferenceverificationstatus(g.getGeoreferenceverificationstatus().getGeoreferenceverificationstatus().equals("") ? null : new GeoreferenceverificationstatusJpaController().save(g.getGeoreferenceverificationstatus()));

        return g;
    }

    public Localityelement fillLocality(Localityelement l) {
        for (Continent i : cacheContinent) {
            if (l.getContinent().getContinent().equals(i.getContinent())) {
                l.setContinent(i);
                break;
            }
        }
        //l.setContinent(l.getContinent().getContinent().equals("") ? null : new ContinentJpaController().save(l.getContinent()));
        for (Country i : cacheCountry) {
            if (l.getCountry().getCountry().equals(i.getCountry())) {
                l.setCountry(i);
                break;
            }
        }
        //l.setCountry(l.getCountry().getCountry().equals("") ? null : new CountryJpaController().save(l.getCountry()));
        for (County i : cacheCounty) {
            if (l.getCounty().getCounty().equals(i.getCounty())) {
                l.setCounty(i);
                break;
            }
        }
        //l.setCounty(l.getCounty().getCounty().equals("") ? null : new CountyJpaController().save(l.getCounty()));
        for (Island i : cacheIsland) {
            if (l.getIsland().getIsland().equals(i.getIsland())) {
                l.setIsland(i);
                break;
            }
        }
        //l.setIsland(l.getIsland().getIsland().equals("") ? null : new IslandJpaController().save(l.getIsland()));
        for (Islandgroup i : cacheIslandgroup) {
            if (l.getIslandgroup().getIslandgroup().equals(i.getIslandgroup())) {
                l.setIslandgroup(i);
                break;
            }
        }
        //l.setIslandgroup(l.getIslandgroup().getIslandgroup().equals("") ? null : new IslandgroupJpaController().save(l.getIslandgroup()));
        for (Locality i : cacheLocality) {
            if (l.getLocality().getLocality().equals(i.getLocality())) {
                l.setLocality(i);
                break;
            }
        }
        //l.setLocality(l.getLocality().getLocality().equals("") ? null : new LocalityJpaController().save(l.getLocality()));
        for (Municipality i : cacheMunicipality) {
            if (l.getMunicipality().getMunicipality().equals(i.getMunicipality())) {
                l.setMunicipality(i);
                break;
            }
        }
        //l.setMunicipality(l.getMunicipality().getMunicipality().equals("") ? null : new MunicipalityJpaController().save(l.getMunicipality()));
        //l.setSite(l.getSite().getSite().equals("")?null:new SiteJpaController().save(l.getSite()));
        for (Stateprovince i : cacheStateprovince) {
            if (l.getStateprovince().getStateprovince().equals(i.getStateprovince())) {
                l.setStateprovince(i);
                break;
            }
        }
        //l.setStateprovince(l.getStateprovince().getStateprovince().equals("") ? null : new StateprovinceJpaController().save(l.getStateprovince()));
        for (Waterbody i : cacheWaterbody) {
            if (l.getWaterbody().getWaterbody().equals(i.getWaterbody())) {
                l.setWaterbody(i);
                break;
            }
        }
        //l.setWaterbody(l.getWaterbody().getWaterbody().equals("") ? null : new WaterbodyJpaController().save(l.getWaterbody()));

        for (Georeferencedby aux : l.getGeoreferencedbyList()) {
            if (!aux.getGeoreferencedby().equals("")) {
                for (Georeferencedby j : cacheGeoreferencedbyNN) {
                    if (j.getGeoreferencedby().equals(aux.getGeoreferencedby())) {
                        aux.setIdgeoreferencedby(j.getIdgeoreferencedby());
                        break;
                    }
                }
            }
        }

        for (Georeferencesource aux : l.getGeoreferencesourceList()) {
            if (!aux.getGeoreferencesource().equals("")) {
                for (Georeferencesource j : cacheGeoreferencesourceNN) {
                    if (j.getGeoreferencesource().equals(aux.getGeoreferencesource())) {
                        aux.setIdgeoreferencesource(j.getIdgeoreferencesource());
                        break;
                    }
                }
            }
        }

        return l;
    }

    public void saveInteraction(List<Interaction> listInteraction) {
        StringBuffer sql = new StringBuffer();
        int count=0;

        for (Interaction i : listInteraction) {
            sql = sql.append("SELECT interactionsave2('")
                    .append(i.getSpecimen1().getRecordlevelelement().getGlobaluniqueidentifier()).append("', '")
                    .append(i.getSpecimen().getRecordlevelelement().getGlobaluniqueidentifier()).append("', '")
                    .append(i.getInteractiontype().getInteractiontype()).append("', '")
                    .append(i.getInteractionrelatedinformation()).append("'); ");
            
            count++;
            if(count >=100){
                execute(sql.toString());
                sql = new StringBuffer();
                count=0;
                //System.out.println("Interagiu");
            }
        }

        //System.out.println(sql.toString());
        if(!sql.toString().equals("")){
            execute(sql.toString());
            //System.out.println("Interagiu");
        }
            
    }

    public Map<String, Integer> findIdSpecimenAndElementsByGUI(Specimen sp) {
        // logica de consutar por GUI
        Map<String, Integer> m = new HashMap<String, Integer>();
        m.put("idspecimen", null);
        try {
            Statement stm = conn.createStatement();
            ResultSet resultset = stm.executeQuery("select * from specimen s left join recordlevelelement r ON s.idrecordlevelelement = r.idrecordlevelelement Where r.globaluniqueidentifier = '" + sp.getRecordlevelelement().getGlobaluniqueidentifier() + "'");
            while (resultset.next()) {
                m.put("idspecimen", resultset.getInt("idspecimen"));
                m.put("idrecordlevelelement", resultset.getInt("idrecordlevelelement"));
                m.put("idoccurrenceelement", resultset.getInt("idoccurrenceelement"));
                m.put("idlocalityelement", resultset.getInt("idlocalityelement"));
                m.put("idtaxonomicelement", resultset.getInt("idtaxonomicelement"));
                m.put("idgeospatialelement", resultset.getInt("idgeospatialelement"));
                m.put("ideventelement", resultset.getInt("ideventelement"));
                m.put("ididentificationelement", resultset.getInt("ididentificationelement"));
            }
            //System.out.println(sp.getRecordlevelelement().getGlobaluniqueidentifier()+" - "+m.get("idspecimen"));
        } catch (Exception ex) {
            Logger.getLogger(DatabaseHandling.class.getName()).log(Level.SEVERE, null, ex);
        }

        return m;
    }

    public Interaction findInteractionBySpecimens(Interaction inteSS) {
        Interaction inteDB = new Interaction();
        Interactiontype interactionType = new Interactiontype();
        inteDB.setIdinteraction(null);
        try {
            Statement stm = conn.createStatement();
            StringBuffer sqlInt =
                    new StringBuffer("select * from interaction i left join interactiontype it on i.idinteractiontype =  it.idinteractiontype Where i.idspecimen1 = ").append(inteSS.getSpecimen1().getIdspecimen()).
                    append(" and i.idspecimen2 = ").
                    append(inteSS.getSpecimen().getIdspecimen()).
                    append(" and it.idinteractiontype = ").
                    append(inteSS.getInteractiontype().getIdinteractiontype());
            ResultSet resultset = stm.executeQuery(sqlInt.toString());
            while (resultset.next()) {
                inteDB.setIdinteraction(resultset.getInt("idinteraction"));
                inteDB.setInteractionrelatedinformation(resultset.getString("interactionrelatedinformation"));
                interactionType.setInteractiontype(resultset.getString("interactiontype"));
                interactionType.setIdinteractiontype(resultset.getInt("idinteractiontype"));
                inteDB.setInteractiontype(interactionType);
            }
        } catch (Exception ex) {
            Logger.getLogger(DatabaseHandling.class.getName()).log(Level.SEVERE, null, ex);
        }

        return inteDB;
    }

    public void executeUpdates(String sql) {
        try {
            Statement stm = conn.createStatement();
            stm.executeUpdate(sql);
        } catch (Exception ex) {
            Logger.getLogger(DatabaseHandling.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void execute(String sql) {
        try {
            Statement stm = conn.createStatement();
            stm.execute(sql);
        } catch (Exception ex) {
            Logger.getLogger(DatabaseHandling.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
